package com.techthinker.dreamBuilder.Dictionary;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.techthinker.dreamBuilder.Database.DatabaseHelper;
import com.techthinker.dreamBuilder.Define.Constant;
import com.techthinker.dreamBuilder.Entity.Dictionary;
import com.techthinker.dreamBuilder.Entity.Lists;
import com.techthinker.dreamBuilder.Entity.Word;

public class WordDAL {
	// private static final String TAG = "WordService";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;
	private static final String KEY_LIST_ID = "list_id";
	private static final String KEY_SPELL = "spell";
	private static final String KEY_PRONUNCIATION = "pronunciation";
	private static final String KEY_EN_EXPLAIN = "enExplain";
	private static final String KEY_ZH_EXPLAIN = "zhExplain";
	private static final String KEY_DETAIL = "detail";
	private static final String KEY_RATE = "rate";
	private static final String KEY_NOTEBOOK_ID = "notebook_id";
	private static final String KEY_ROWID = "word_id";

	public WordDAL(Context ctx) {
		this.mCtx = ctx;
	}

	public WordDAL open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx, Constant.DB_NAME);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void createWordTableByDicName(String dicName) {
		mDb.execSQL(Constant.SQL_CREATE_WORD_1 + Constant.TABLE_WORD + dicName
				+ Constant.SQL_CREATE_WORD_2);
	}

	public void close() {
		mDbHelper.close();
	}

	public boolean isClosed() {
		if (mDbHelper == null)
			return true;
		else
			return false;
	}

	// create a word item
	public long createWord(Word word) {
		String dicName = word.getLists().getDictionary().getName()
				+ "_"
				+ word.getLists().getDictionary().getVersion()
						.replace(' ', '_');
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_LIST_ID, word.getLists().getList_id());
		initialValues.put(KEY_SPELL, word.getSpell());
		initialValues.put(KEY_PRONUNCIATION, word.getPronunciation());
		initialValues.put(KEY_EN_EXPLAIN, word.getEnExplain());
		initialValues.put(KEY_ZH_EXPLAIN, word.getZhExplain());
		initialValues.put(KEY_DETAIL, word.getDetail());
		initialValues.put(KEY_RATE, word.getRate());
		initialValues.put(KEY_NOTEBOOK_ID, word.getNotebook()
				.getDictionary_id());
		return mDb.insert(Constant.TABLE_WORD + dicName + "\"", null,
				initialValues);
	}

	// update a word item
	public boolean updateWord(Word word) {
		String dicName = word.getLists().getDictionary().getName()
				+ "_"
				+ word.getLists().getDictionary().getVersion()
						.replace(' ', '_');
		ContentValues args = new ContentValues();
		args.put(KEY_LIST_ID, word.getLists().getList_id());
		args.put(KEY_SPELL, word.getSpell());
		args.put(KEY_PRONUNCIATION, word.getPronunciation());
		args.put(KEY_EN_EXPLAIN, word.getEnExplain());
		args.put(KEY_ZH_EXPLAIN, word.getZhExplain());
		args.put(KEY_DETAIL, word.getDetail());
		args.put(KEY_RATE, word.getRate());
		args.put(KEY_NOTEBOOK_ID, word.getNotebook().getDictionary_id());
		return mDb.update(Constant.TABLE_WORD + dicName + "\"", args, KEY_ROWID
				+ "=" + word.getWord_id(), null) > 0;
	}

	// delete a word item
	public boolean deleteWord(Word word) {
		String dicName = word.getLists().getDictionary().getName()
				+ "_"
				+ word.getLists().getDictionary().getVersion()
						.replace(' ', '_');
		return mDb.delete(Constant.TABLE_WORD + dicName + "\"", KEY_ROWID + "="
				+ word.getWord_id(), null) > 0;
	}

	// delete word table by the dictionary_id
	public void deleteWordsByDictionary(int dictionary_id) {
		// fetch the related dictionary
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		Dictionary tempDic = dicDAL.fetchDictionaryById(dictionary_id);
		dicDAL.close();
		String dicName = tempDic.getName() + "_"
				+ tempDic.getVersion().replace(' ', '_');
		mDb.execSQL(Constant.SQL_DELETE_WORD + Constant.TABLE_WORD + dicName
				+ "\";");
	}

	// retrieve all words by list id
	public List<Word> fetchAllWordsByListId(int listId) {
		Lists tempList;
		Dictionary notebook;

		// fetch the related list
		ListDAL listDAL = new ListDAL(mCtx);
		listDAL.open();
		tempList = listDAL.fetchListsById(listId);
		listDAL.close();
		String dicName = tempList.getDictionary().getName() + "_"
				+ tempList.getDictionary().getVersion().replace(' ', '_');

		Cursor mCursor = mDb.query(Constant.TABLE_WORD + dicName + "\"",
				new String[] { KEY_ROWID, KEY_LIST_ID, KEY_SPELL,
						KEY_PRONUNCIATION, KEY_EN_EXPLAIN, KEY_ZH_EXPLAIN,
						KEY_DETAIL, KEY_RATE, KEY_NOTEBOOK_ID }, KEY_LIST_ID
						+ "=" + tempList.getList_id(), null, null, null, null);
		// fetch the related notebook
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		notebook = dicDAL.fetchDictionaryById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_NOTEBOOK_ID)));
		dicDAL.close();

		List<Word> wordsList = new ArrayList<Word>();

		while (mCursor.moveToNext()) {
			wordsList.add(retrieveWord(mCursor, tempList, notebook));
		}
		mCursor.close();
		return wordsList;
	}

	// retrieve all words by dictionary id
	public List<Word> fetchAllWordsByDictionaryId(int dicId) {
		Dictionary tempDic;
		Lists tempList;
		Dictionary notebook;

		// fetch the related list
		DictionaryDAL dDAL = new DictionaryDAL(mCtx);
		dDAL.open();
		tempDic = dDAL.fetchDictionaryById(dicId);
		dDAL.close();
		String dicName = tempDic.getName() + "_"
				+ tempDic.getVersion().replace(' ', '_');
		Cursor mCursor = mDb.query(Constant.TABLE_WORD + dicName + "\"",
				new String[] { KEY_ROWID, KEY_LIST_ID, KEY_SPELL,
						KEY_PRONUNCIATION, KEY_EN_EXPLAIN, KEY_ZH_EXPLAIN,
						KEY_DETAIL, KEY_RATE, KEY_NOTEBOOK_ID }, null, null,
				null, null, null);

		// fetch the related list
		ListDAL listDAL = new ListDAL(mCtx);
		listDAL.open();
		tempList = listDAL.fetchListsById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_LIST_ID)));
		listDAL.close();
		// fetch the related notebook
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		notebook = dicDAL.fetchDictionaryById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_NOTEBOOK_ID)));
		dicDAL.close();

		List<Word> wordsList = new ArrayList<Word>();
		while (mCursor.moveToNext()) {
			wordsList.add(retrieveWord(mCursor, tempList, notebook));
		}
		mCursor.close();
		return wordsList;
	}

	// retrieve all words by dictionary name
	public List<Word> fetchAllWordsByDicName(String dicName) {
		Lists tempList;
		Dictionary notebook;

		Cursor mCursor = mDb.query(Constant.TABLE_WORD + dicName + "\"",
				new String[] { KEY_ROWID, KEY_LIST_ID, KEY_SPELL,
						KEY_PRONUNCIATION, KEY_EN_EXPLAIN, KEY_ZH_EXPLAIN,
						KEY_DETAIL, KEY_RATE, KEY_NOTEBOOK_ID }, null, null,
				null, null, null);

		// fetch the related list
		ListDAL listDAL = new ListDAL(mCtx);
		listDAL.open();
		tempList = listDAL.fetchListsById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_LIST_ID)));
		listDAL.close();
		// fetch the related notebook
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		notebook = dicDAL.fetchDictionaryById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_NOTEBOOK_ID)));
		dicDAL.close();

		List<Word> wordsList = new ArrayList<Word>();
		while (mCursor.moveToNext()) {

			wordsList.add(retrieveWord(mCursor, tempList, notebook));
		}
		mCursor.close();
		return wordsList;
	}

	// retrieve all words by dictionary name
	public Cursor fetchAllWordsToCursorByDicName(String dicName) {
		return mDb.query(Constant.TABLE_WORD + dicName + "\"", new String[] {
				KEY_ROWID, KEY_LIST_ID, KEY_SPELL, KEY_PRONUNCIATION,
				KEY_EN_EXPLAIN, KEY_ZH_EXPLAIN, KEY_DETAIL, KEY_RATE,
				KEY_NOTEBOOK_ID }, null, null, null, null, null);
	}

	// retrieve a word item by id and dictionary name
	public Word fetchWordById(long rowId, String dicName) throws SQLException {
		Lists tempList;
		Dictionary notebook;

		Cursor mCursor = mDb.query(true, Constant.TABLE_WORD + dicName + "\"",
				new String[] { KEY_ROWID, KEY_LIST_ID, KEY_SPELL,
						KEY_PRONUNCIATION, KEY_EN_EXPLAIN, KEY_ZH_EXPLAIN,
						KEY_DETAIL, KEY_RATE, KEY_NOTEBOOK_ID }, KEY_ROWID
						+ "=" + rowId, null, null, null, null, null);

		// fetch the related list
		ListDAL listDAL = new ListDAL(mCtx);
		listDAL.open();
		tempList = listDAL.fetchListsById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_LIST_ID)));
		listDAL.close();
		// fetch the related notebook
		DictionaryDAL dicDAL = new DictionaryDAL(mCtx);
		dicDAL.open();
		notebook = dicDAL.fetchDictionaryById(mCursor.getInt(mCursor
				.getColumnIndex(KEY_NOTEBOOK_ID)));
		dicDAL.close();

		if (mCursor.moveToFirst()) {

			return retrieveWord(mCursor, tempList, notebook);
		}
		mCursor.close();
		return null;

	}

	// retrieve a Word
	private Word retrieveWord(Cursor mCursor, Lists tempList,
			Dictionary notebook) {
		Word word = new Word();
		word.setWord_id(mCursor.getInt(mCursor.getColumnIndex(KEY_ROWID)));
		word.setLists(tempList);
		word.setSpell(mCursor.getString(mCursor.getColumnIndex(KEY_SPELL)));
		word.setPronunciation(mCursor.getString(mCursor
				.getColumnIndex(KEY_PRONUNCIATION)));
		word.setEnExplain(mCursor.getString(mCursor
				.getColumnIndex(KEY_EN_EXPLAIN)));
		word.setZhExplain(mCursor.getString(mCursor
				.getColumnIndex(KEY_ZH_EXPLAIN)));
		word.setDetail(mCursor.getString(mCursor.getColumnIndex(KEY_DETAIL)));
		word.setRate(mCursor.getInt(mCursor.getColumnIndex(KEY_RATE)));
		word.setNotebook(notebook);
		return word;
	}
}
